package com.yzw.mybatisutils.service;

import com.yzw.mybatisutils.model.Column;
import com.yzw.mybatisutils.model.DataBase;
import com.yzw.mybatisutils.model.Table;
import com.yzw.mybatisutils.uitls.DataBaseUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author：yzw
 * @Date：2017/11/29
 * @描述：
 */
public class DataBaseService {
	private static final Logger LOGGER = Logger.getLogger(DataBaseService.class);

	/**
	 * 根据数据库获取表名
	 *
	 * @param dataBase
	 * @return
	 */
	public static List<String> getTableList(DataBase dataBase) {
		List<String> tableList = new ArrayList<>();
		DatabaseMetaData data = getMetaData(dataBase);
		ResultSet resultSet;
		try {
			if (data != null) {
				resultSet = data.getTables(null, "%", "%", null);
				while (resultSet.next()) {
					tableList.add(resultSet.getString("TABLE_NAME"));
				}
			}
		} catch (Exception e) {
			LOGGER.error(e);
		}
		return tableList;
	}

	/**
	 * 根据数据库、表名获取表信息
	 *
	 * @param tableName
	 * @param db
	 * @return
	 */
	public static Table getTableByName(String tableName, DataBase db) {

		DatabaseMetaData metaData = getMetaData(db);
		try {
			if (metaData != null) {

				ResultSet tableSet = metaData.getTables(null, "%", tableName, new String[]{"TABLE"});
				Table table = null;
				if (tableSet.next()) {
					String tmpTableName = tableSet.getString("TABLE_NAME");
					String tmpTableComment = tableSet.getString("REMARKS");

					if (StringUtils.isNotEmpty(tmpTableName)) {
						table = new Table(tmpTableName, tmpTableComment, null, null);
					}
				}
				if (table == null) {
					return null;
				}

				ResultSet columnSet = metaData.getColumns(null, "%", tableName, "%");

				List<Column> columnList = new ArrayList<Column>();
				String columnName, columnComment, columnType;
				while (columnSet.next()) {
					columnName = columnSet.getString("COLUMN_NAME");
					columnComment = columnSet.getString("REMARKS");
					columnType = columnSet.getString("DATA_TYPE");
					columnList.add(new Column(columnName, columnComment, columnType));
				}
				table.setColumns(columnList);
				return table;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	private static DatabaseMetaData getMetaData(DataBase db) {
		Connection conn;
		try {
			conn = DataBaseUtil.getConnection(db);
			return conn.getMetaData();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 转化表字段的数据类型
	 *
	 * @param table
	 * @return
	 */
	public static Map<String, Object> convertColumnType(Table table) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put("table", table);
		List<String> packageList = new ArrayList<String>();
		if (table != null && table.getColumns() != null && table.getColumns().size() > 0) {
			List<Column> newColumns = new ArrayList<>();
			for (Column column : table.getColumns()) {
				column.setPropertyName(capColumnName(column.getColumnName()));
				switch (Integer.parseInt(column.getColumnType())) {
					case Types.LONGNVARCHAR:
						column.setColumnType("LONGNVARCHAR");
						column.setJavaType("String");
						break;
					case Types.INTEGER:
						column.setColumnType("INTEGER");
						column.setJavaType("Integer");
						break;
					case Types.BIGINT:
						column.setColumnType("BIGINT");
						column.setJavaType("Long");
						break;
					case Types.REAL:
						column.setColumnType("REAL");
						column.setJavaType("Float");
						break;
					case Types.FLOAT:
						column.setColumnType("FLOAT");
						column.setJavaType("Float");
						break;
					case Types.DOUBLE:
						column.setColumnType("DOUBLE");
						column.setJavaType("Double");
						break;
					case Types.NUMERIC:
						column.setColumnType("NUMERIC");
						column.setJavaType("BigDecimal");
						packageList.add("java.math.BigDecimal");
						break;
					case Types.DECIMAL:
						column.setColumnType("DECIMAL");
						column.setJavaType("BigDecimal");
						packageList.add("java.math.BigDecimal");
						break;
					case Types.BIT:
						column.setColumnType("BIT");
						column.setJavaType("Boolean");
						break;
					case Types.BOOLEAN:
						column.setColumnType("BOOLEAN");
						column.setJavaType("Boolean");
						break;
					case Types.TINYINT:
					case Types.BINARY:
						column.setColumnType("TINYINT");
						column.setJavaType("Integer");
						break;
					case Types.DATE:
						column.setColumnType("DATE");
						column.setJavaType("Date");
						packageList.add("java.util.Date");
						break;
					case Types.TIME:
						column.setColumnType("TIME");
						column.setJavaType("Date");
						packageList.add("java.util.Date");
						break;
					case Types.TIMESTAMP:
						column.setColumnType("TIMESTAMP");
						column.setJavaType("Date");
						packageList.add("java.util.Date");
						break;
					default:
						column.setColumnType("VARCHAR");
						column.setJavaType("String");
				}
				newColumns.add(column);
			}
			table.setColumns(newColumns);
		}
		resultMap.put("packageList", new ArrayList<>(new HashSet<>(packageList)));
		return resultMap;
	}

	/**
	 * 将user_name转化为userName类型
	 *
	 * @param columnName
	 * @return
	 */
	public static String capColumnName(String columnName) {

		String[] arr = StringUtils.split(columnName, "_");
		if (arr != null && arr.length > 0) {
			StringBuilder strBuilder = new StringBuilder(arr[0]);
			for (int i = 1; i < arr.length; i++) {
				strBuilder.append(StringUtils.capitalize(arr[i]));
			}
			return strBuilder.toString();
		}
		return columnName;
	}

	/**
	 * 获取mysql的主键
	 *
	 * @param con
	 * @param tableName
	 * @return
	 */
	public static String[] getPrimaryKey(Connection con, String tableName) {
		String sql = "SHOW CREATE TABLE " + tableName;
		try {
			PreparedStatement pre = con.prepareStatement(sql);
			ResultSet rs = pre.executeQuery();
			if (rs.next()) {
				//正则匹配数据
				Pattern pattern = Pattern.compile("PRIMARY KEY \\(`(.*)`\\)");
				Matcher matcher = pattern.matcher(rs.getString(2));
				matcher.find();
				String data = matcher.group();
				//过滤对于字符
				data = data.replaceAll("`|PRIMARY KEY \\(|\\)", "");
				//拆分字符
				return data.split(",");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void main(String[] args) {
		DataBase dataBase = new DataBase("127.0.0.1", "3306", "monitordb", "mysql",  "root" , "root");
		try {
			Connection connection = DataBaseUtil.getConnection(dataBase);
			String[] primaryKey = getPrimaryKey(connection, "monitor_cfg_host");
			System.err.println(primaryKey);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
}
